/* eslint-disable class-methods-use-this */
/* eslint-disable prefer-template */

import { SqlHelper } from '../../lib/utils/sqlHelper'
import { DbColumn } from '../../models/dbColumn'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbTable } from '../../models/dbTable'
import { DbProvider, line } from '../core/dbProvider'

export class Db2Provider extends DbProvider {
	private sqlStatementEnd = ' @'

	private outputIDFirstSection = 1

	public getCreateTableIfNotExistsSql(model: DbModel): string {
		this.outputInitialize()
		this.outputSetTerminator()
		this.getSQLCreateTableInternal(model, false)
		return this.outputCopy()
	}

	public getCreateTableSql(model: DbModel, withComment: boolean): string {
		this.outputInitialize()
		this.outputSetTerminator()
		this.getSQLCreateTableInternal(model, withComment)
		return this.outputCopy()
	}

	public getDropTableSql(model: DbModel): string {
		this.outputInitialize()
		this.outputSetTerminator()
		// Drop table SQL
		this.getSQLDropTableInternal(model)
		return this.outputCopy()
	}

	public getDropThenCreateTableSql(model: DbModel, withComment: boolean): string {
		this.outputInitialize()
		this.outputSetTerminator()

		// Drop table SQL
		this.getSQLDropTableInternal(model)

		// Create tables SQL
		this.getSQLCreateTableInternal(model, withComment)
		return this.outputCopy()
	}

	private getSQLCreateTableInternal(model: DbModel, withComment: boolean) {
		// Create tables
		model.tables.forEach((table) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSQLCreateTableSingle(table, withComment)
			this.outputlnln(sqlCreateTable)
			if (sqlCreateFK.length > 0) {
				this.outputlnln(sqlCreateFK, this.outputIDFirstSection)
			}
		})
	}

	public getSQLCreateTableSingle(
		table: DbTable,
		withComment: boolean,
	): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = {
			sqlCreateFK: '',
			sqlCreateTable: '',
		}

		if (!table.columns) {
			return result
		}
		// Create Columns sql
		let sqlColumn: string
		sqlColumn = ''

		table.columns.forEach((column: DbColumn, index) => {
			const sqlDefault =
				column.default && column.default.length > 0 ? ` DEFAULT ${column.default}` : ''
			sqlColumn +=
				line +
				`  ${index === 0 ? ' ' : ','}${column.name} ${column.type} ${
					column.nullable ? 'NULL' : 'NOT NULL'
				}${sqlDefault}`
		})

		// Primary key SQL
		let sqlPrimaryKey: string
		sqlPrimaryKey = ''
		if (table.primaryKey && table.primaryKey.columns) {
			sqlPrimaryKey =
				line + `  ,CONSTRAINT ${this.renderPkName(table)} PRIMARY KEY (${table.primaryKey.columns})`
		}

		// Unique Indexes SQL
		let sqlUniqueIndex: string
		sqlUniqueIndex = ''
		table.indexes?.forEach((dbIndex: DbIndex) => {
			if (dbIndex.unique) {
				const sqlCluster = dbIndex.cluster ? ' CLUSTER' : ''
				sqlUniqueIndex +=
					line +
					`CREATE UNIQUE INDEX ${this.renderIndexName(table, dbIndex)} ON ${table.name} (${
						dbIndex.columns
					})${sqlCluster}` +
					this.sqlStatementEnd
			}
		})

		// non-unique Indexes SQL
		let sqlNoUniqueIndex: string
		sqlNoUniqueIndex = ''
		table.indexes?.forEach((dbIndex: DbIndex) => {
			if (!dbIndex.unique) {
				const sqlCluster = dbIndex.cluster ? ' CLUSTER' : ''
				sqlNoUniqueIndex +=
					line +
					`CREATE INDEX ${this.renderIndexName(table, dbIndex)} ON ${table.name} (${
						dbIndex.columns
					})${sqlCluster}` +
					this.sqlStatementEnd
			}
		})

		// Generate table sql
		result.sqlCreateTable =
			`-- Create table '${table.name}'` +
			line +
			`CREATE TABLE ${table.name} (` +
			`${sqlColumn}` +
			`${sqlPrimaryKey}` +
			line +
			')' +
			this.sqlStatementEnd +
			`${sqlUniqueIndex}` +
			`${sqlNoUniqueIndex}`

		// Generate Foreign key SQL
		result.sqlCreateFK = ''
		if (table.foreignKeys && table.foreignKeys.length > 0) {
			result.sqlCreateFK = `-- Foreign keys for table '${table.name}'` + line
		}

		table.foreignKeys?.forEach((foreignKey: DbForeignKey, index) => {
			result.sqlCreateFK +=
				`${index === 0 ? '' : line}ALTER TABLE ${table.name}` +
				line +
				`  ADD CONSTRAINT ${this.renderFkName(table, foreignKey)}` +
				line +
				`  FOREIGN KEY (${foreignKey.columns})` +
				line +
				`  REFERENCES ${foreignKey.refTable}(${foreignKey.refColumns})${
					foreignKey.options ? ' ' + foreignKey.options : ''
				}` +
				this.sqlStatementEnd
		})

		// Table comment
		let sqlComment: string
		sqlComment = ''

		if (withComment && table.comment!.length > 0) {
			sqlComment =
				line +
				`COMMENT ON TABLE ${table.name} IS ${SqlHelper.toSQL(table.comment)}` +
				this.sqlStatementEnd
		}

		// Column comment
		if (withComment) {
			table.columns.forEach((column) => {
				const sqlDescription = this.getColumnComment(column)
				if (sqlDescription.length > 0) {
					sqlComment +=
						line +
						`COMMENT ON COLUMN ${table.name}.${column.name} ` +
						`IS ${SqlHelper.toSQL(sqlDescription)}` +
						this.sqlStatementEnd
				}
			})
		}

		if (sqlComment.length > 0) {
			result.sqlCreateTable += line + sqlComment
		}

		return result
	}

	public getSQLDropTableInternal(model: DbModel) {
		// Drop tables
		model.tables.forEach((table) => {
			const sSQL = this.getSQLDropTableSingle(table)
			this.outputlnln(sSQL)
		})
	}

	private getSQLDropTableSingle(table: DbTable): string {
		const tableName = table.name.toUpperCase()
		return (
			`-- Drop table '${table.name}'` +
			line +
			'BEGIN' +
			line +
			'  IF EXISTS (SELECT * FROM SYSCAT.TABLES' +
			line +
			'             WHERE TABSCHEMA = CURRENT_SCHEMA' +
			line +
			`             AND TABNAME = '${tableName}') THEN` +
			line +
			`    EXECUTE IMMEDIATE 'DROP TABLE ${table.name}';` +
			line +
			'  END IF;' +
			line +
			'END' +
			this.sqlStatementEnd
		)
	}

	private outputSetTerminator() {
		const sSQL = '--#SET TERMINATOR' + this.sqlStatementEnd
		this.outputlnln(sSQL)
	}
}
